Set-Location c:\scripts
$MaxElapsedMs = 20000 ## number of milliseconds to wait until a query is cancelled (20 seconds)
Write-Output ("Cancelling queries running more than {0} seconds." -f ($MaxElapsedMs/1000))

## Fire the DMV and pickup the running sessions
$sql_sessions = 'SELECT * FROM $system.discover_sessions WHERE session_status = 1'
$sessions = .\Invoke-DMV.ps1 -Sql $sql_sessions

$numCancel = 0
foreach ($s in $sessions) 
{
    if ($s.session_last_command -ne $sql_sessions) { ## don't cancel myself :-) 
    
        $sql_commands = ('SELECT TOP 1 * FROM $system.discover_commands WHERE session_spid = {0}' -f $s.session_spid)
        $commands = .\Invoke-DMV.ps1 -Sql $sql_commands
        foreach($c in $commands) {
            if ($c.command_elapsed_time_MS -gt $MaxElapsedMs) { ## The real test itself 
                $result = .\invoke-cancelsession.ps1 -spid $s.session_spid ## cancel the session
                Write-Output ("`nCancelled SPID({0}):`nCommand: {1}`n
                    Username: {2}
                    Start time: {3}
                    Elapsed time (MS): {4}
                    Reads: {5}
                    Writes: {6} " -f 
                    $s.session_spid, $s.session_last_command, $s.session_user_name, $c.command_start_time, 
                    $c.command_elapsed_time_MS, $c.command_reads, $c.command_writes )
                $numCancel += 1
            }
        }
    }
}

if ($numCancel -eq 0) {
    Write-Output "No queries cancelled."
} elseif ($numCancel -eq 1) {
    Write-Output "`nCancelled 1 query."
} else { 
    Write-Output ("`nCancelled {0} queries." -f $numCancel) 
}